package io.gitee.bluemiaomiao.dataanalyzebackend.controller;

import io.gitee.bluemiaomiao.dataanalyzebackend.repos.Table.ExcelMapper;
import io.gitee.bluemiaomiao.dataanalyzebackend.util.ResultCodeUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Administrator
 * @description Excel操作
 */
@RestController
@RequestMapping(value = "/excel")
public class ExcelController {

  /**
   * @author: TLang
   * @description 获取Mapper实例
   * @create time: 2020/10/16 14:10
   */
  @Autowired private ExcelMapper excelMapper;

  NumberFormat numberFormat = NumberFormat.getInstance();

  /**
   * @author: TLang
   * @description 用来标识插入数据的百分比
   * @create time: 2020/10/16 14:10
   */
  // 商城会员
  private volatile String TimerIUser = new String();
  // 会员消费明细
  private volatile String SaleItemsIUser = new String();
  // 门店运营分析
  private volatile String TimerTShopAn = new String();

  /**
   * @author: TLang
   * @description 获取会员消费表传来的文件数据并进行解析
   * @param file1 获取前端传来的文件数据
   * @return CommonResult.success
   * @create time: 2020/10/16 14:12
   */
  @PostMapping("/SaleItemsInsert")
  public ResultCodeUtil<String> TableInsertSaleItems(
      @RequestParam(value = "file", required = false) MultipartFile file1) {
    SaleItemsIUser = "0";
    numberFormat.setMaximumFractionDigits(2);
    String fileName = file1.getOriginalFilename();
    Workbook workbook = null;
    try {
      // 判断什么类型文件
      if (fileName.endsWith(".xls")) {
        workbook = new HSSFWorkbook(file1.getInputStream());
      } else if (fileName.endsWith(".xlsx")) {
        workbook = new XSSFWorkbook(file1.getInputStream());
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    if (workbook == null) {
    } else {
      // 获取所有的工作表的的数量
      int numOfSheet = workbook.getNumberOfSheets();
      // 遍历表
      for (int i = 0; i < numOfSheet; i++) {
        // 获取一个sheet也就是一个工作本。
        Sheet sheet = workbook.getSheetAt(i);
        if (sheet == null) {
          continue;
        }
        // 获取一个sheet有多少Row
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum == 0) {
          continue;
        }

        Row row;
        ArrayList<String> Title = new ArrayList<String>();
        List<Map> InsertArray = new ArrayList<Map>();
        row = sheet.getRow(2);
        if (row == null) {
          continue;
        }
        // 获取一个Row有多少Cell
        short lastCellNum = row.getLastCellNum();
        for (int k = 0; k <= lastCellNum; k++) {
          if (row.getCell(k) == null) {
            continue;
          }
          Cell cell = row.getCell(k);
          cell.setCellType(CellType.STRING);
          String res = cell.getStringCellValue().trim();
          // 打印出cell(单元格的内容)
          Title.add(res);
        }

        // 遍历Row
        for (int j = 3; j <= lastRowNum; j++) {
          Map Nr_Map = new HashMap<String, String>();
          row = sheet.getRow(j);
          if (row == null) {
            continue;
          }
          // 获取一个Row有多少Cell
          short lastCellNum_two = row.getLastCellNum();
          for (int k = 0; k <= lastCellNum; k++) {
            if (row.getCell(k) == null) {
              continue;
            }
            Cell cell = row.getCell(k);
            cell.setCellType(CellType.STRING);
            String res = cell.getStringCellValue().trim();
            // 打印出cell(单元格的内容)
            Nr_Map.put(Title.get(k), res);
          }
          InsertArray.add(Nr_Map);
          if ((j % 1000) == 999 || j == lastRowNum) {
            excelMapper.saveTSaleItems(InsertArray);
            SaleItemsIUser = numberFormat.format((float) j / (float) lastRowNum * 100);
            InsertArray.clear();
          }
        }
      }
    }
    return ResultCodeUtil.success("成功导入");
  }

  /**
   * @author: TLang
   * @description 获取传来的文件商城会员表数据并进行解析
   * @param file1 获取前端传来的文件数据
   * @return CommonResult.success
   * @create time: 2020/10/16 14:12
   */
  @PostMapping("/TUserInsert")
  public ResultCodeUtil<String> TableInsertTUser(
      @RequestParam(value = "file", required = false) MultipartFile file1) {
    TimerIUser = "0";
    numberFormat.setMaximumFractionDigits(2);
    String fileName = file1.getOriginalFilename();
    Workbook workbook = null;
    try {
      // 判断什么类型文件
      if (fileName.endsWith(".xls")) {
        workbook = new HSSFWorkbook(file1.getInputStream());
      } else if (fileName.endsWith(".xlsx")) {
        workbook = new XSSFWorkbook(file1.getInputStream());
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    if (workbook == null) {
    } else {
      // 获取所有的工作表的的数量
      int numOfSheet = workbook.getNumberOfSheets();
      // 遍历表
      for (int i = 0; i < numOfSheet; i++) {
        // 获取一个sheet也就是一个工作本。
        Sheet sheet = workbook.getSheetAt(i);
        if (sheet == null) {
          continue;
        }
        // 获取一个sheet有多少Row
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum == 0) {
          continue;
        }

        Row row;
        ArrayList<String> Title = new ArrayList<String>();
        List<Map> InsertArray = new ArrayList<Map>();
        row = sheet.getRow(1);
        if (row == null) {
          continue;
        }
        // 获取一个Row有多少Cell
        short lastCellNum = row.getLastCellNum();
        for (int k = 0; k <= lastCellNum; k++) {
          if (row.getCell(k) == null) {
            continue;
          }
          Cell cell = row.getCell(k);
          cell.setCellType(CellType.STRING);
          String res = cell.getStringCellValue().trim();
          // 打印出cell(单元格的内容)
          Title.add(res);
        }

        for (int j = 2; j <= lastRowNum; j++) {
          HashMap Nr_Map = new HashMap<String, String>();
          row = sheet.getRow(j);
          if (row == null) {
            continue;
          }
          // 获取一个Row有多少Cell
          short lastCellNum_two = row.getLastCellNum();
          for (int k = 0; k <= lastCellNum; k++) {
            if (row.getCell(k) == null) {
              continue;
            }
            Cell cell = row.getCell(k);
            cell.setCellType(CellType.STRING);
            String res = cell.getStringCellValue().trim();
            // 打印出cell(单元格的内容)
            System.out.println(res);
            Nr_Map.put(Title.get(k), res);
          }
          InsertArray.add(Nr_Map);
          if ((j % 1000) == 999 || j == lastRowNum) {
            excelMapper.saveTUser(InsertArray);
            TimerIUser = numberFormat.format((float) j / (float) lastRowNum * 100);
            InsertArray.clear();
          }
        }
      }
    }
    return ResultCodeUtil.success("成功导入");
  }

  /**
   * @author: TLang
   * @description 获取传来的文件门店分析表数据并进行解析
   * @param file1 获取前端传来的文件数据
   * @return CommonResult.success
   * @create time: 2020/10/16 14:12
   */
  @PostMapping("/TShopAnInsert")
  public ResultCodeUtil<String> TableInsertTShopAn(
      @RequestParam(value = "file", required = false) MultipartFile file1) {
    TimerIUser = "0";
    numberFormat.setMaximumFractionDigits(2);
    String fileName = file1.getOriginalFilename();
    Workbook workbook = null;
    try {
      // 判断什么类型文件
      if (fileName.endsWith(".xls")) {
        workbook = new HSSFWorkbook(file1.getInputStream());
      } else if (fileName.endsWith(".xlsx")) {
        workbook = new XSSFWorkbook(file1.getInputStream());
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    if (workbook == null) {
    } else {
      // 获取所有的工作表的的数量
      int numOfSheet = workbook.getNumberOfSheets();
      // 遍历表
      for (int i = 0; i < numOfSheet; i++) {
        // 获取一个sheet也就是一个工作本。
        Sheet sheet = workbook.getSheetAt(i);
        if (sheet == null) {
          continue;
        }
        // 获取一个sheet有多少Row
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum == 0) {
          continue;
        }

        Row row;
        ArrayList<String> Title = new ArrayList<String>();
        row = sheet.getRow(1);
        if (row == null) {
          continue;
        }
        // 获取一个Row有多少Cell
        short lastCellNum = row.getLastCellNum();
        // 开始遍历行
        for (int k = 0; k <= lastCellNum; k++) {
          if (k >= 13) {
            row = sheet.getRow(2);
          }
          if (row.getCell(k) == null) {
            continue;
          }
          Cell cell = row.getCell(k);
          cell.setCellType(CellType.STRING);
          String res = cell.getStringCellValue().trim();
          // 打印出cell(单元格的内容)
          if ("".equals(res)) {
          } else {
            Title.add(res);
          }
        }

        List<Map> InsertArray = new ArrayList<Map>();
        for (int j = 3; j <= lastRowNum; j++) {
          HashMap Nr_Map = new HashMap<String, String>();
          row = sheet.getRow(j);
          if (row == null) {
            continue;
          }
          // 获取一个Row有多少Cell
          short lastCellNum_two = row.getLastCellNum();
          for (int k = 0; k <= lastCellNum; k++) {
            if (row.getCell(k) == null) {
              continue;
            }
            Cell cell = row.getCell(k);
            cell.setCellType(CellType.STRING);
            String res = cell.getStringCellValue().trim();
            // 打印出cell(单元格的内容)
            Nr_Map.put(Title.get(k), res);
          }
          if (!(j == lastRowNum)) {
            InsertArray.add(Nr_Map);
          }
          if ((j % 1000) == 999 || j == lastRowNum) {
            excelMapper.saveTShopAn(InsertArray);
            TimerTShopAn = numberFormat.format((float) j / (float) lastRowNum * 100);
            InsertArray.clear();
            if (j == lastRowNum) {
              TimerTShopAn = "100";
            }
          }
        }
      }
    }
    return ResultCodeUtil.success("成功导入");
  }

  /**
   * @author: TLang
   * @description 删除会员消费表中的数据
   * @param befor,after 时间段
   * @return CommonResult.success
   * @create time: 2020/10/16 14:12
   */
  @PostMapping("/SaleItemsDelete")
  public ResultCodeUtil<String> SaleItemsDelete(
      @RequestParam("befor") String befor, @RequestParam("after") String after) {
    excelMapper.deleteTSaleItems(befor, after);
    return ResultCodeUtil.success("删除成功");
  }

  /**
   * @author: TLang
   * @description 删除商城会员表中的数据
   * @param befor,after 时间段
   * @return CommonResult.success
   * @create time: 2020/10/16 14:12
   */
  @PostMapping("/TUserDelete")
  public ResultCodeUtil<String> TUserDelete(
      @RequestParam("befor") String befor, @RequestParam("after") String after) {
    excelMapper.deleteTUser(befor, after);
    return ResultCodeUtil.success("删除成功");
  }

  /**
   * @author: TLang
   * @description 删除门店分析表中的数据
   * @param befor,after 时间段
   * @return CommonResult.success
   * @create time: 2020/10/16 14:12
   */
  @PostMapping("/TShopAnDelete")
  public ResultCodeUtil<String> TShopAnDelete(
      @RequestParam("befor") String befor, @RequestParam("after") String after) {
    excelMapper.deleteTShopAn(befor, after);
    return ResultCodeUtil.success("删除成功");
  }

  /**
   * @author: TLang
   * @description 获取当前商城会员表导入进度
   * @return TimerIUser
   * @create time: 2020/10/16 14:19
   */
  @PostMapping("/TUserInsertTimer")
  public String TUserInsertTimer() {

    return TimerIUser;
  }

  /**
   * @author: TLang
   * @description 获取当前会员消费信息表导入进度
   * @return SaleItemsIUser
   * @create time: 2020/10/16 14:19
   */
  @PostMapping("/SaleItemsTimer")
  public String SaleItemsTimer() {

    return SaleItemsIUser;
  }

  /**
   * @author: TLang
   * @description 获取当前门店运营分析表导入进度
   * @return TimerTShopAn
   * @create time: 2020/10/16 14:19
   */
  @PostMapping("/TimerTShopAn")
  public String TimerTShopAn() {

    return TimerTShopAn;
  }
}
